/*
 * DbAccess.cpp
 *
 *  Created on: Jan 28, 2013
 *      Author: root
 */

#include "DbAccess.h"

/*
 * sql define
 * create connecton table,and table index
 * flag:failure 0, success 1
 * status:session live AUTH_WAIT 0->1,AUTH completed 2,AUTH_DISCONN_WAIT 3->4,AUTH_DISCONN completed 5
 * */
static const char * create_connection_table_sql = "CREATE TABLE  connections (   \n"
    "  id INTEGER PRIMARY KEY, \n"
    "  Node_Id       INTEGER default 0,  \n"
    "  Conn_Id       INTEGER default 0,  \n"
    "  CleintIP      VARCHAR(64) default '127.0.0.1',  \n"
    "  SessionId     VARCHAR(128) default '0' ,  \n"
    "  File_Name     TEXT ,  \n"
    "  Url           TEXT,   \n"
	"  Total_KB      BIGINT default 0,   \n"
	"  Total_Secs    INTEGER default 0,  \n"
	"  CreatDateTime DATETIME  default CURRENT_TIMESTAMP,  \n"
	"  SessionBeginTime   BIGINT  default 0, \n"
	"  SessionEndTime     BIGINT  default 0, \n"
	"  flag          INTEGER  default 0, \n"
	"  status        INTEGER  default 0)";
/*
 * sql define
 * create connecton table index
 * */
static const char * create_connection_index = "create index session_index on connections(SessionId)";

/*
 * create file table
 */
static const char * create_file_table_sql = "CREATE TABLE nodefiles (   \n"
    "  id INTEGER PRIMARY KEY, \n"
	"  FileId     VARCHAR(32) default '0' ,  \n"
	"  File_Name  VARCHAR(1024) default '0' ,  \n"
	"  File_Name_Hash     VARCHAR(32) default '0' ,  \n"
	"  File_Hash     	  VARCHAR(32) default '0' ,  \n"
	"  File_Size          BIGINT default 0,   \n"
	"  CurrentFileSize    BIGINT default 0,  \n"
	"  CreateTime         BIGINT  default 0, \n"
	"  UpdateTime         BIGINT  default 0, \n"
	"  Status             INTEGER  default 0, \n"
	"  OpFlag             INTEGER  default 0, \n"
	"  CreatDateTime DATETIME  default CURRENT_TIMESTAMP)";

/*
 * sql define
 * create connecton table index
 * */
static const char * create_file_index = "create index file_index on nodefiles(File_Name)";

/**
 * DbAccess class
 */
DbAccess::DbAccess()
{
	this->open_flag = 0;
	memset(this->connectdb,'\0',sizeof(this->connectdb));
	memset(this->filedb,'\0',sizeof(this->filedb));

	pSvrlog = NULL;
	pSvrConfig = NULL;
}

/**
 * DbAccess class free
 */
DbAccess::~DbAccess() {

	table_pool_lock.Lock();
	{
		  connection_table_row * pRow = NULL;
		  while(this->free_table_pool.size() > 0)
		  {
			  pRow = free_table_pool.top();
			  free_table_pool.pop();

			  if(pRow != NULL)
			  {
				  delete pRow;
				  pRow = NULL;
			  }
		  }

		  table_pool_lock.Unlock();
	}
}

/**
 * init db access operation buf
 */
int DbAccess::init(Svr_log * psvrlog,SvrConfig * pconfig)
{
    if(psvrlog == NULL || pconfig == NULL)
        return FAILURE;


    this->pSvrlog = psvrlog;
    this->pSvrConfig = pconfig;

    /*
     * set db file name
     * */
    strcpy(this->connectdb,pconfig->dbpath);
    strcat(this->connectdb,"/xcdnsvr.db");

    strcpy(this->filedb,pconfig->dbpath);
    strcat(this->filedb,"/files.db");


    /*init data row pool*/
    table_pool_lock.Lock();
    {
        for(int i = 0;i < this->pSvrConfig->svr_handle_threads ;i++)
        {
        	connection_table_row * row = new connection_table_row();
            if(row != NULL)
            {
                free_table_pool.push(row);
            }
        }

        table_pool_lock.Unlock();
    }


    return SUCCESS;
}

/*
 * connect databse
 */
int DbAccess::connectodb()
{
    int ret = -1;
    /*lock*/
    connectdblock.Lock();
    {
        if(open_flag == 0)
        {
			/*check db is exist,and init database*/
			if(access(connectdb,F_OK) != 0)
			{
				ret = db_sqlite_exec(create_connection_table_sql,this->connectdb);
				/*
				 * create table index
				 */
				if(ret == 200)
				{
					db_sqlite_exec(create_connection_index,this->connectdb);
				}
			}
			else
			{
				/*end*/
				ret = 200;
			}

            /*check connection status*/
            if(ret == 200)
            {
                open_flag = 1;
            }
        }
        else
        {
            /**do nothing**/
            ret = 200;
        }

        /*unlock*/
        connectdblock.Unlock();
    }

    return ret;
}

int DbAccess::execconnectionnosql(const char * sqlstr)
{
    int ret = -1;
    if(open_flag == 0 || sqlstr == NULL  || strlen(sqlstr) == 0)
        return ret;

    /*lock*/
    connectdblock.Lock();
    {
        /*open db connect*/
        ret = db_sqlite_exec(sqlstr,this->connectdb);

        connectdblock.Unlock();
    }

    return ret;
}

int DbAccess::querryconnection(const char * sqlstr,connection_table_row * pRow)
{
	  int ret = -1;
	  if(open_flag == 0 || sqlstr == NULL  || strlen(sqlstr) == 0 || pRow == NULL)
		  return ret;

	  /*lock*/
	  connectdblock.Lock();
	  {
			/*open db connect*/
			sqlite3 *db = NULL;
			ret = sqlite3_open(connectdb, &db);
			if (ret !=  SQLITE_OK || db == NULL)
			{
				this->pSvrlog->log_fmt(LOG_ERR,"Could not open database: %d,%s,db:%s\n", ret,sqlite3_errmsg(db),connectdb);
				ret = 403;
				sqlite3_close(db);
			}
			else
			{
			   sqlite3_stmt *stmt;
			   ret= sqlite3_prepare(db,sqlstr, strlen(sqlstr), &stmt,0);
			   if (ret != SQLITE_OK)
			   {
					this->pSvrlog->log_fmt(LOG_ERR,"Can't open statement: %s/n", sqlite3_errmsg(db));
					ret =  503;
			   }
			   else
			   {
					char * CleintIP = NULL;
					char * SessionId = NULL;
					char * filename = NULL;
					char * url = NULL;
					char * Create_Time = NULL;

					while(sqlite3_step(stmt) == SQLITE_ROW )
					{
						  pRow->id = sqlite3_column_int(stmt, 0);
						  pRow->Node_Id = sqlite3_column_int(stmt, 1);
						  pRow->Conn_Id  = sqlite3_column_int(stmt, 2);

						  CleintIP = (char *)sqlite3_column_text(stmt,3);
						  if(CleintIP != NULL && (strlen(CleintIP) < sizeof(pRow->CleintIP)))
							  strcpy(pRow->CleintIP,CleintIP);

						  SessionId = (char *)sqlite3_column_text(stmt,4);
						  if(SessionId != NULL && (strlen(SessionId) < sizeof(pRow->SessionId)))
							  strcpy(pRow->SessionId,SessionId);

						  filename = (char *)sqlite3_column_text(stmt,5);
						  if(filename != NULL && (strlen(filename) < sizeof(pRow->File_Name)))
							  strcpy(pRow->File_Name,filename);

						  url = (char *)sqlite3_column_text(stmt,6);
						  if(url != NULL && (strlen(url) < sizeof(pRow->Url)))
							  strcpy(pRow->Url,url);

						  pRow->Total_KB  = sqlite3_column_int64(stmt,7);
						  pRow->Total_Secs =  sqlite3_column_int(stmt,8);

						  Create_Time = (char *)sqlite3_column_text(stmt,9);
						  if(Create_Time != NULL && (strlen(Create_Time) < sizeof(pRow->CreatDateTime)))
							  strcpy(pRow->CreatDateTime,Create_Time);

						  pRow->SessionBeginTime  = (long)sqlite3_column_int64(stmt,10);
						  pRow->SessionEndTime  = (long)sqlite3_column_int64(stmt,11);
						  pRow->flag =  sqlite3_column_int(stmt,12);
						  pRow->status =  sqlite3_column_int(stmt,13);

						  pRow->fit_flag = 1;

						  /*only one recorder*/
						  ret = 200;
						  break;
					}

				   /*free*/
				   sqlite3_finalize(stmt);
			   }

			   /*close db*/
			   sqlite3_close(db);
			}
			/*
			 * unlock
			 */
		   connectdblock.Unlock();
	  }

	  return ret;
}

int DbAccess::db_sqlite_exec(const char * sqlstr,const char * dbname)
{
     int ret = -1;
     sqlite3 *db = NULL;
     ret = sqlite3_open(dbname, &db);
     if (ret !=  SQLITE_OK || db == NULL)
     {
          this->pSvrlog->log_fmt(LOG_ERR,"Could not open database: %d,%s, dbname:%s\n",ret, sqlite3_errmsg(db), dbname);
          ret = 403;
          sqlite3_close(db);
     }
     else
     {
          ret = sqlite3_exec(db,sqlstr,NULL,NULL,NULL);
          if (ret != SQLITE_OK)
          {
        	  this->pSvrlog->log_fmt(LOG_ERR,"SQL error: %s\n sql:%s,dbname:%s\n", sqlite3_errmsg(db), sqlstr, dbname);
              ret = 503;
          }
          else
          {
              ret = 200;
          }

          /*close db*/
          sqlite3_close(db);
     }

     return ret;
}


/**
 * alloc_vodconnection_row_buf
 */
int DbAccess::alloc_connection_row_buf(connection_table_row ** pRow)
{
    if(pRow == NULL || (*pRow) != NULL)
        return -1;
    table_pool_lock.Lock();
    {
          if(this->free_table_pool.size() > 0)
          {
              *pRow = free_table_pool.top();
              free_table_pool.pop();
          }
          else
          {
              *pRow = new connection_table_row();
          }
          table_pool_lock.Unlock();
    }

    return SUCCESS;
}

/**
 * reset_vodconnection_row_buf
 */
void DbAccess::reset_connection_row_buf(connection_table_row * pRow)
{
    if(pRow == NULL)
       return ;

    pRow->id = 0;
    pRow->Node_Id = 0;
    pRow->Conn_Id = 0;
	memset(pRow->CleintIP,'\0',sizeof(pRow->CleintIP));
	memset(pRow->SessionId,'\0',sizeof(pRow->SessionId));
	memset(pRow->File_Name,'\0',sizeof(pRow->File_Name));
	memset(pRow->Url,'\0',sizeof(pRow->Url));
	pRow->Total_KB = 0;
	pRow->Total_Secs = 0;
	memset(pRow->CreatDateTime,'\0',sizeof(pRow->CreatDateTime));
	pRow->SessionBeginTime = 0;
	pRow->SessionEndTime = 0;
	pRow->flag = 0;
	pRow->status = 0;

	/**
	* fit_flag:1,means struct data has been seted.
	*/
	pRow->fit_flag = 0;
}

/**
 * free_vodconnection_row_buf
 */
void DbAccess::free_connection_row_buf(connection_table_row * pRow)
{
    if(pRow == NULL)
        return ;
    /**
     * reset
     */
    reset_connection_row_buf(pRow);

    table_pool_lock.Lock();
    {
        this->free_table_pool.push(pRow);

        table_pool_lock.Unlock();
    }
}


/**
 * server file table
 */
int DbAccess::openfiledb()
{
	int ret = -1;
	filedblock.Lock();
	{
		/*check db is exist,and init database*/
		if(access(filedb,F_OK) != 0)
		{
			ret = db_sqlite_exec(create_file_table_sql,this->filedb);
			/*
			 * create table index
			 */
			if(ret == 200)
			{
				db_sqlite_exec(create_file_index,this->filedb);
			}
		}
		else
		{
			/*end*/
			ret = 200;
		}

		filedblock.Unlock();
	}
	return ret;
}

int DbAccess::querryfileinfo(const char * sqlstr,file_table_row * pRow)
{
	/*
	 * open db
	 * */
	if(this->openfiledb() != 200)
	{
		return -1;
	}
	/**
	 * querryfileinfo
	 */
	int ret = -1;
	filedblock.Lock();
	{
		/*open db connect*/
		sqlite3 *db = NULL;
		ret = sqlite3_open(filedb, &db);
		if (ret !=  SQLITE_OK || db == NULL)
		{
			this->pSvrlog->log_fmt(LOG_ERR,"Could not open database: %d,%s,db:%s\n", ret,sqlite3_errmsg(db),filedb);
			ret = 403;
			sqlite3_close(db);
		}
		else
		{
		   sqlite3_stmt *stmt;
		   ret= sqlite3_prepare(db,sqlstr, strlen(sqlstr), &stmt,0);
		   if (ret != SQLITE_OK)
		   {
				this->pSvrlog->log_fmt(LOG_ERR,"Can't open statement: %s/n", sqlite3_errmsg(db));
				ret =  503;
		   }
		   else
		   {
				char * FileId = NULL;
				char * File_Name = NULL;
				char * File_Name_Hash = NULL;
				char * File_Hash = NULL;

				while(sqlite3_step(stmt) == SQLITE_ROW )
				{
					  pRow->id = sqlite3_column_int(stmt, 0);

					  FileId = (char *)sqlite3_column_text(stmt,1);
					  if(FileId != NULL && (strlen(FileId) < sizeof(pRow->FileId)))
						  strcpy(pRow->FileId,FileId);

					  File_Name = (char *)sqlite3_column_text(stmt,2);
					  if(File_Name != NULL && (strlen(File_Name) < sizeof(pRow->File_Name)))
						  strcpy(pRow->File_Name,File_Name);

					  File_Name_Hash = (char *)sqlite3_column_text(stmt,3);
					  if(File_Name_Hash != NULL && (strlen(File_Name_Hash) < sizeof(pRow->File_Name_Hash)))
						  strcpy(pRow->File_Name_Hash,File_Name_Hash);

					  File_Hash = (char *)sqlite3_column_text(stmt,4);
					  if(File_Hash != NULL && (strlen(File_Hash) < sizeof(pRow->File_Hash)))
						  strcpy(pRow->File_Hash,File_Hash);

					  pRow->File_Size  = sqlite3_column_int64(stmt,5);
					  pRow->CurrentFileSize  = sqlite3_column_int64(stmt,6);
					  pRow->CreateTime  = (long)sqlite3_column_int64(stmt,7);
					  pRow->UpdateTime  = (long)sqlite3_column_int64(stmt,8);
					  pRow->Status =  sqlite3_column_int(stmt,9);
					  pRow->OpFlag =  sqlite3_column_int(stmt,10);

					  pRow->fit_flag = 1;

					  /*only one recorder*/
					  ret = 200;
					  break;
				}

			   /*free*/
			   sqlite3_finalize(stmt);
		   }

		   /*close db*/
		   sqlite3_close(db);
		}
		/*end*/
		filedblock.Unlock();
	}
	return ret;
}

int DbAccess::updatefileinfonosql(const char * sqlstr)
{
	/*
	 * open db
	 * */
	if(this->openfiledb() != 200)
	{
		return -1;
	}

	int ret = -1;
	filedblock.Lock();
	{
		 /*open db connect*/
		 ret = db_sqlite_exec(sqlstr,this->filedb);

		 filedblock.Unlock();
	}
	return ret;
}

int DbAccess::updatefilesizenosql(long long CurrentFileSize,const char *  File_Name)
{
	/*
	 * open db
	 * */
	if(this->openfiledb() != 200)
	{
		return -1;
	}

	int ret = -1;
	char sqlstr[1024];
	memset(sqlstr,'\0',sizeof(sqlstr));
	sprintf(sqlstr, "UPDATE nodefiles set CurrentFileSize = %llu , UpdateTime = %ld where File_Name = '%s'",
		 CurrentFileSize,
		 (long)time(NULL),
		 File_Name);

	ret = this->db_sqlite_exec(sqlstr,filedb);

	return ret;
}

int DbAccess::addfileinfo(const char * FileId,
			const char *  File_Name,
			const char *  File_Name_Hash,
			const char *  File_Hash,
			long long File_Size,
			long long CurrentFileSize,
			int  Status,
			int  OpFlag)
{
	/*
	* open db
	* */
	if(this->openfiledb() != 200)
	{
		return -1;
	}

	int ret = -1;

	/*
	* querry is have file
	*/
	file_table_row dr;
	reset_file_row_buf(&dr);
	char sqlstr[1024];
	memset(sqlstr,'\0',sizeof(sqlstr));
	sprintf(sqlstr,"select * from nodefiles where File_Name = '%s' limit 0,1",File_Name);
	this->querryfileinfo(sqlstr,&dr);
	if(!dr.fit_flag)
	{
		 memset(sqlstr,'\0',sizeof(sqlstr));
		 sprintf(sqlstr,"INSERT INTO nodefiles(FileId,File_Name,File_Name_Hash,File_Hash,File_Size,CurrentFileSize,CreateTime,UpdateTime,Status,OpFlag)"
				 " VALUES('%s','%s','%s','%s',%lld ,%lld ,%ld, %ld,%d ,%d)",
				 FileId,
				 File_Name,
				 File_Name_Hash,
				 File_Hash,
				 File_Size,
				 CurrentFileSize,
				 (long)time(NULL),
				 (long)time(NULL),
				 Status,
				 OpFlag);

		 ret = this->db_sqlite_exec(sqlstr,filedb);
	}
	else
	{
		ret = 200;
	}

	return ret;
}


void DbAccess::reset_file_row_buf(file_table_row * pRow)
{
	if(pRow == NULL)
		return ;

	pRow->id = 0;
	memset(pRow->FileId,'\0',sizeof(pRow->FileId));
	memset(pRow->File_Name,'\0',sizeof(pRow->File_Name));
	memset(pRow->File_Name_Hash,'\0',sizeof(pRow->File_Name_Hash));
	memset(pRow->File_Hash,'\0',sizeof(pRow->File_Hash));
	pRow->File_Size = 0;
	pRow->CurrentFileSize = 0;
	pRow->CreateTime = 0;
	pRow->UpdateTime = 0;
	pRow->Status = 0;
	pRow->OpFlag = 0;
	pRow->fit_flag = 0;
}
